﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
namespace homework17
{
    internal class Program
    {
        private static string connectionString = "Data Source=student_management.db";
        static void Main(string[] args)
        {
            CreateDatabaseAndTables();
            Console.WriteLine("请选择操作：1. 添加学校 2. 修改学校 3. 删除学校 4. 显示学校 5. 添加班级 6. 修改班级 7. 删除班级 8. 显示班级 9. 添加学生 10. 显示学生 11. 修改学生 12. 删除学生 13. 显示日志 14. 退出");
            while (true)
            {
                
                var choice = Console.ReadLine();
                if (choice == "1")
                {
                    AddSchool();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "2")
                {
                    UpdateSchool();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "3")
                {
                    DeleteSchool();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "4")
                {
                    DisplaySchools();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "5")
                {
                    AddClass();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "6")
                {
                    UpdateClass();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "7")
                {
                    DeleteClass();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "8")
                {
                    DisplayClasses();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "9")
                {
                    AddStudent();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "10")
                {
                    DisplayStudents();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "11")
                {
                    UpdateStudent();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "12")
                {
                    DeleteStudent();
                    Console.WriteLine("Continue...");
                }
                else if (choice == "13")
                {
                    DisplayLogs();
                }
                else if(choice=="14")
                {
                    break;
                }
            }
        }

        private static void CreateDatabaseAndTables()
        {
            SQLiteConnection.CreateFile("student_management.db");
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string createSchoolsTable = @"
                    CREATE TABLE IF NOT EXISTS Schools (
                        SchoolId INTEGER PRIMARY KEY AUTOINCREMENT,
                        SchoolName TEXT NOT NULL
                    )";

                string createClassesTable = @"
                    CREATE TABLE IF NOT EXISTS Classes (
                        ClassId INTEGER PRIMARY KEY AUTOINCREMENT,
                        ClassName TEXT NOT NULL,
                        SchoolId INTEGER,
                        FOREIGN KEY (SchoolId) REFERENCES Schools (SchoolId)
                    )";

                string createStudentsTable = @"
                    CREATE TABLE IF NOT EXISTS Students (
                        StudentId INTEGER PRIMARY KEY AUTOINCREMENT,
                        StudentName TEXT NOT NULL,
                        ClassId INTEGER,
                        FOREIGN KEY (ClassId) REFERENCES Classes (ClassId)
                    )";

                string createLogsTable = @"
                    CREATE TABLE IF NOT EXISTS Logs (
                        LogId INTEGER PRIMARY KEY AUTOINCREMENT,
                        Operation TEXT NOT NULL,
                        Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
                    )";

                using (var command = new SQLiteCommand(createSchoolsTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createClassesTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createStudentsTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createLogsTable, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
        private static void AddSchool()
        {
            Console.Write("请输入学校名称：");
            string schoolName = Console.ReadLine();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO Schools (SchoolName) VALUES (@name)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", schoolName);
                    command.ExecuteNonQuery();
                }
            }
            LogOperation("Added a new school: " + schoolName);
        }
        private static void UpdateSchool()
        {
            Console.Write("请输入要修改的学校ID：");
            int schoolId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Schools WHERE SchoolId = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", schoolId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"当前学校名称: {reader["SchoolName"]}");
                            Console.Write("请输入新的学校名称：");
                            string newSchoolName = Console.ReadLine();

                            reader.Close();

                            string updateQuery = "UPDATE Schools SET SchoolName = @name WHERE SchoolId = @id";
                            using (var updateCommand = new SQLiteCommand(updateQuery, connection))
                            {
                                updateCommand.Parameters.AddWithValue("@name", newSchoolName);
                                updateCommand.Parameters.AddWithValue("@id", schoolId);
                                updateCommand.ExecuteNonQuery();
                            }
                            LogOperation($"Updated school ID {schoolId} to {newSchoolName}");
                            Console.WriteLine("学校信息更新成功！");
                        }
                        else
                        {
                            Console.WriteLine("未找到指定ID的学校！");
                        }
                    }
                }
            }
        }
        private static void DeleteSchool()
        {
            Console.Write("请输入要删除的学校ID：");
            int schoolId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Schools WHERE SchoolId = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", schoolId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"确定要删除学校: {reader["SchoolName"]} 吗? (y/n)");
                            string confirm = Console.ReadLine();
                            if (confirm.ToLower() == "y")
                            {
                                reader.Close();
                                string deleteQuery = "DELETE FROM Schools WHERE SchoolId = @id";
                                using (var deleteCommand = new SQLiteCommand(deleteQuery, connection))
                                {
                                    deleteCommand.Parameters.AddWithValue("@id", schoolId);
                                    deleteCommand.ExecuteNonQuery();
                                }
                                LogOperation($"Deleted school ID {schoolId}");
                                Console.WriteLine("学校信息删除成功！");
                            }
                            else
                            {
                                Console.WriteLine("操作已取消。");
                            }
                        }
                        else
                        {
                            Console.WriteLine("未找到指定ID的学校！");
                        }
                    }
                }
            }
        }
        private static void DisplaySchools()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Schools";
                using (var command = new SQLiteCommand(query, connection))
                using (var reader = command.ExecuteReader())
                {
                    int i = 0;
                    while (reader.Read())
                    {
                        i++;
                        Console.WriteLine($"ID: {reader["SchoolId"]}, Name: {reader["SchoolName"]}");
                    }
                    if(i==0)
                    {
                        Console.WriteLine("当前学校列表为空！请先添加学校信息！");
                    }
                }
            }
        }



        private static void AddClass()
        {
            Console.Write("请输入班级名称：");
            string className = Console.ReadLine();
            Console.Write("请输入学校ID：");
            int schoolId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO Classes (ClassName, SchoolId) VALUES (@name, @schoolId)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", className);
                    command.Parameters.AddWithValue("@schoolId", schoolId);
                    command.ExecuteNonQuery();
                }
            }
            LogOperation("Added a new class: " + className);
        }
        private static void UpdateClass()
        {
            Console.Write("请输入要修改的班级ID：");
            int classId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Classes WHERE ClassId = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", classId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"当前班级名称: {reader["ClassName"]}, 学校ID: {reader["SchoolId"]}");
                            Console.Write("请输入新的班级名称：");
                            string newClassName = Console.ReadLine();
                            Console.Write("请输入新的学校ID：");
                            int newSchoolId = int.Parse(Console.ReadLine());

                            reader.Close();

                            string updateQuery = "UPDATE Classes SET ClassName = @name, SchoolId = @schoolId WHERE ClassId = @id";
                            using (var updateCommand = new SQLiteCommand(updateQuery, connection))
                            {
                                updateCommand.Parameters.AddWithValue("@name", newClassName);
                                updateCommand.Parameters.AddWithValue("@schoolId", newSchoolId);
                                updateCommand.Parameters.AddWithValue("@id", classId);
                                updateCommand.ExecuteNonQuery();
                            }
                            LogOperation($"Updated class ID {classId} to {newClassName} with school ID {newSchoolId}");
                            Console.WriteLine("班级信息更新成功！");
                        }
                        else
                        {
                            Console.WriteLine("未找到指定ID的班级！");
                        }
                    }
                }
            }
        }
        private static void DeleteClass()
        {
            Console.Write("请输入要删除的班级ID：");
            int classId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Classes WHERE ClassId = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", classId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"确定要删除班级: {reader["ClassName"]} 吗? (y/n)");
                            string confirm = Console.ReadLine();
                            if (confirm.ToLower() == "y")
                            {
                                reader.Close();
                                string deleteQuery = "DELETE FROM Classes WHERE ClassId = @id";
                                using (var deleteCommand = new SQLiteCommand(deleteQuery, connection))
                                {
                                    deleteCommand.Parameters.AddWithValue("@id", classId);
                                    deleteCommand.ExecuteNonQuery();
                                }
                                LogOperation($"Deleted class ID {classId}");
                                Console.WriteLine("班级信息删除成功！");
                            }
                            else
                            {
                                Console.WriteLine("操作已取消。");
                            }
                        }
                        else
                        {
                            Console.WriteLine("未找到指定ID的班级！");
                        }
                    }
                }
            }
        }
        private static void DisplayClasses()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Classes";
                using (var command = new SQLiteCommand(query, connection))
                using (var reader = command.ExecuteReader())
                {
                    int i = 0;
                    while (reader.Read())
                    {
                        i++;
                        Console.WriteLine($"ID: {reader["ClassId"]}, Name: {reader["ClassName"]}, School ID: {reader["SchoolId"]}");
                    }
                    if(i==0)
                    {
                        Console.WriteLine("当前班级列表为空！请先添加班级信息！");
                    }
                }
            }
        }
        private static void AddStudent()
        {
            Console.Write("请输入学生姓名：");
            string studentName = Console.ReadLine();
            Console.Write("请输入班级ID：");
            int classId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO Students (StudentName, ClassId) VALUES (@name, @classId)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", studentName);
                    command.Parameters.AddWithValue("@classId", classId);
                    command.ExecuteNonQuery();
                }
            }
            LogOperation("Added a new student: " + studentName);
        }
        private static void DisplayStudents()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = @"
            SELECT s.StudentId, s.StudentName, c.ClassName, sch.SchoolName
            FROM Students s
           LEFT JOIN Classes c ON s.ClassId = c.ClassId
           LEFT JOIN Schools sch ON c.SchoolId = sch.SchoolId";
                using (var command = new SQLiteCommand(query, connection))
                using (var reader = command.ExecuteReader())
                {
                    int i = 0;
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["StudentId"]}, Name: {reader["StudentName"]}, Class: {reader["ClassName"]}, School: {reader["SchoolName"]}");
                        i++;
                    }
                    if (i == 0)
                    {
                        Console.WriteLine("当前学生列表为空！请先添加学生信息！");
                    }
                }
            }
        }
        private static void UpdateStudent()
        {
            Console.Write("请输入要修改的学生ID：");
            int studentId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Students WHERE StudentId = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", studentId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"当前学生姓名: {reader["StudentName"]}, 班级ID: {reader["ClassId"]}");
                            Console.Write("请输入新的学生姓名：");
                            string newStudentName = Console.ReadLine();
                            Console.Write("请输入新的班级ID：");
                            int newClassId = int.Parse(Console.ReadLine());

                            reader.Close();

                            string updateQuery = "UPDATE Students SET StudentName = @name, ClassId = @classId WHERE StudentId = @id";
                            using (var updateCommand = new SQLiteCommand(updateQuery, connection))
                            {
                                updateCommand.Parameters.AddWithValue("@name", newStudentName);
                                updateCommand.Parameters.AddWithValue("@classId", newClassId);
                                updateCommand.Parameters.AddWithValue("@id", studentId);
                                updateCommand.ExecuteNonQuery();
                            }
                            LogOperation($"Updated student ID {studentId} to {newStudentName} with class ID {newClassId}");
                            Console.WriteLine("学生信息更新成功！");
                        }
                        else
                        {
                            Console.WriteLine("未找到指定ID的学生！");
                        }
                    }
                }


            }
        }
        private static void DeleteStudent()
        {
            Console.Write("请输入要删除的学生ID：");
            int studentId = int.Parse(Console.ReadLine());

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Students WHERE StudentId = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", studentId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"确定要删除学生: {reader["StudentName"]} 吗? (y/n)");
                            string confirm = Console.ReadLine();
                            if (confirm.ToLower() == "y")
                            {
                                reader.Close();
                                string deleteQuery = "DELETE FROM Students WHERE StudentId = @id";
                                using (var deleteCommand = new SQLiteCommand(deleteQuery, connection))
                                {
                                    deleteCommand.Parameters.AddWithValue("@id", studentId);
                                    deleteCommand.ExecuteNonQuery();
                                }
                                LogOperation($"Deleted student ID {studentId}");
                                Console.WriteLine("学生信息删除成功！");
                            }
                            else
                            {
                                Console.WriteLine("操作已取消。");
                            }
                        }
                        else
                        {
                            Console.WriteLine("未找到指定ID的学生！");
                        }
                    }
                }
            }
        }
        private static void LogOperation(string operation)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO Logs (Operation) VALUES (@operation)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@operation", operation);
                    command.ExecuteNonQuery();
                }
            }
        }
        private static void DisplayLogs()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Logs ORDER BY Timestamp DESC";
                using (var command = new SQLiteCommand(query, connection))
                using (var reader = command.ExecuteReader())
                {
                    Console.WriteLine("操作日志：");
                    int i = 0;
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["LogId"]}, 操作: {reader["Operation"]}, 时间: {reader["Timestamp"]}");
                        i++;
                    }
                    if(i==0)
                    {
                        Console.WriteLine("日志为空!");
                    }
                    Console.WriteLine("Continue...");
                }
            }
        }

    }
    public class School
    {
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
        public List<Class> Classes { get; set; } = new List<Class>();
    }

    public class Class
    {
        public int ClassId { get; set; }
        public string ClassName { get; set; }
        public int SchoolId { get; set; }
        public List<Student> Students { get; set; } = new List<Student>();
    }

    public class Student
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public int ClassId { get; set; }
    }

}
